<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Cell_DataType;
class Tongjiap extends Common
{
    //AP费用品牌决策依据
    public function ys($cid="",$pid=""){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $this->assign("nian",$nian);
        $pro=db("pro_brands")->where("is_actived=1 and is_deleted=0")->select();
        if(!$pid){
            $pid=db("pro_products")->where("is_actived=1 and is_deleted=0")->value("id");
        }
        $this->assign("pro",$pro);
        $this->assign("pid",$pid);
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();
        foreach ($kaohe1 as $k => $v) {
            # code...
            $kaohe1[$k]["ys"]=0;$kaohe1[$k]["sj"]=0;$kaohe1[$k]["cy"]=0;
            $kaohe1[$k]["start"]=date("Y-m-d",$kaohe1[$k]["starttime"]);
            $kaohe1[$k]["end"]=date("Y-m-d",$kaohe1[$k]["endtime"]);

        }
        //esuseway=6  ap费用6
        $kemu=db("cg_feiyongkemu3")->where("esuseway like '%\"6\"%'")->select();
       // print_r($kemu);
        $rol=count($kemu)*3;

        $this->assign("rol",$rol);
        
        $product=db("pro_brands")->where(["id"=>$pid])->find();
        $this->assign("product",$product);
        $all=["ys"=>0,"sj"=>0,"cy"=>0];
        for($i=0;$i<count($kemu);$i++){
            $kemu[$i]["zys"]=0;$kemu[$i]["zsj"]=0;
            for($j=0;$j<count($kaohe1);$j++){
                //预算
                $ys=db("yusuan")->where("type=2 and jine<>0 and pinpai=$pid and yijikaoheqi={$kaohe1[$j]["id"]} and sanjifeiyong={$kemu[$i]["id"]}")->sum("jine");
                if($ys){
                    $kemu[$i]["ys"][$j]=$ys;
                    $kemu[$i]["zys"]+=$ys;
                    $kaohe1[$j]["ys"]+=$ys;
                    $kaohe1[$j]["cy"]+=$ys;
                    $all["ys"]+=$ys;
                    $all["cy"]+=$ys;
                }else{
                    $kemu[$i]["ys"][$j]=0;
                }
                //实际使用
                $sj=db("ap_apply ap")->join("ap_apply_detail apd","apd.apply_id=ap.id")
                ->where("ap.is_deleted=0 and ap.is_actived=1 and ap.state=7 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and apd.brand=$pid and feiyong_id={$kemu[$i]["id"]} and apd.start_time>='{$kaohe1[$j]["start"]}' and apd.end_time<='{$kaohe1[$j]["end"]}'")->sum("apd.pifuzonge");
                if($sj){
                    $kemu[$i]["sj"][$j]=$sj;
                    $kemu[$i]["zsj"]+=$sj;
                    $kaohe1[$j]["sj"]+=$sj;
                    $kaohe1[$j]["cy"]-=$sj;
                    $all["sj"]+=$sj;
                    $all["cy"]-=$sj;
                }else{
                    $kemu[$i]["sj"][$j]=0;
                }
                //差异
                $kemu[$i]["cy"][$j]=$kemu[$i]["ys"][$j]-$kemu[$i]["sj"][$j];
            }
            $kemu[$i]["zcy"]=$kemu[$i]["zys"]-$kemu[$i]["zsj"];

        }
        $this->assign("all",$all);
        $this->assign("kemu",$kemu);
        $this->assign("kaohe1",$kaohe1);
        return $this->fetch();
    }
    public function ysdc($cid,$pid){
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();
        foreach ($kaohe1 as $k => $v) {
            # code...
            $kaohe1[$k]["ys"]=0;$kaohe1[$k]["sj"]=0;$kaohe1[$k]["cy"]=0;
            $kaohe1[$k]["start"]=date("Y-m-d",$kaohe1[$k]["starttime"]);
            $kaohe1[$k]["end"]=date("Y-m-d",$kaohe1[$k]["endtime"]);

        }
        //esuseway=6  ap费用6
        $kemu=db("cg_feiyongkemu3")->where("esuseway like '%\"6\"%'")->select();
        $product=db("pro_brands")->where(["id"=>$pid])->find();
        $all=["ys"=>0,"sj"=>0,"cy"=>0];
        for($i=0;$i<count($kemu);$i++){
            $kemu[$i]["zys"]=0;$kemu[$i]["zsj"]=0;
            for($j=0;$j<count($kaohe1);$j++){
                //预算
                $ys=db("yusuan")->where("type=2 and jine<>0 and pinpai=$pid and yijikaoheqi={$kaohe1[$j]["id"]} and sanjifeiyong={$kemu[$i]["id"]}")->sum("jine");
                if($ys){
                    $kemu[$i]["ys"][$j]=$ys;
                    $kemu[$i]["zys"]+=$ys;
                    $kaohe1[$j]["ys"]+=$ys;
                    $kaohe1[$j]["cy"]+=$ys;
                    $all["ys"]+=$ys;
                    $all["cy"]+=$ys;
                }else{
                    $kemu[$i]["ys"][$j]=0;
                }
                //实际使用
                $sj=db("ap_apply ap")->join("ap_apply_detail apd","apd.apply_id=ap.id")
                ->where("ap.is_deleted=0 and ap.is_actived=1 and ap.state=7 and ap.tag=0 and apd.is_deleted=0 and apd.is_actived=1 and apd.brand=$pid and feiyong_id={$kemu[$i]["id"]} and apd.start_time>='{$kaohe1[$j]["start"]}' and apd.end_time<='{$kaohe1[$j]["end"]}'")->sum("apd.pifuzonge");
                if($sj){
                    $kemu[$i]["sj"][$j]=$sj;
                    $kemu[$i]["zsj"]+=$sj;
                    $kaohe1[$j]["sj"]+=$sj;
                    $kaohe1[$j]["cy"]-=$sj;
                    $all["sj"]+=$sj;
                    $all["cy"]-=$sj;
                }else{
                    $kemu[$i]["sj"][$j]=0;
                }
                //差异
                $kemu[$i]["cy"][$j]=$kemu[$i]["ys"][$j]-$kemu[$i]["sj"][$j];
            }
            $kemu[$i]["zcy"]=$kemu[$i]["zys"]-$kemu[$i]["zsj"];

        }
        //导出处理
        $eid=Session::get("employee_id");
        $users=Db::table("employee")->where(["id"=>$eid])->find();
        $title="{$nian}-{$product["brand_name"]}-决策依据";
        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
         $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
             $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);
       
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);

       

        
          $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "$title");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:G2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
     
      


         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:G3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:G3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:G3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:G3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"品牌");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"三级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"第一季度");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"第二季度");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"第三季度");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"第四季度");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"小计");
        $h=3;
        $ed=3+count($kemu)*3;
        $objPHPExcel->getActiveSheet()->mergeCells('A4:A'.$ed);
          //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A4',$product["brand_name"],PHPExcel_Cell_DataType::TYPE_STRING);
        for($i=0;$i<count($kemu);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$kemu[$i]["title"]."预算",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$kemu[$i]["ys"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kemu[$i]["ys"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kemu[$i]["ys"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$kemu[$i]["ys"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$kemu[$i]["zys"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$kemu[$i]["title"]."实际",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$kemu[$i]["sj"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kemu[$i]["sj"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kemu[$i]["sj"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$kemu[$i]["sj"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$kemu[$i]["zsj"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"差异",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$kemu[$i]["cy"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kemu[$i]["cy"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kemu[$i]["cy"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$kemu[$i]["cy"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$kemu[$i]["zcy"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $h++;
        $ed=$h+2;
         $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':A'.$ed);
          //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"总计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"品牌预算小计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$kaohe1[0]["ys"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kaohe1[1]["ys"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kaohe1[2]["ys"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$kaohe1[3]["ys"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$all["ys"],PHPExcel_Cell_DataType::TYPE_STRING);
        $h++;
         $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"品牌实际投入小计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$kaohe1[0]["sj"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kaohe1[1]["sj"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kaohe1[2]["sj"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$kaohe1[3]["sj"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$all["sj"],PHPExcel_Cell_DataType::TYPE_STRING);
        $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"品牌投入差异小计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$kaohe1[0]["cy"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kaohe1[1]["cy"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kaohe1[2]["cy"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$kaohe1[3]["cy"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$all["cy"],PHPExcel_Cell_DataType::TYPE_STRING);
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:G'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
             //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
               $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        //var_dump($objWriter);exit;
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //ap预算使用
    public function yssy($cid='',$k1id=0){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->select();
        $this->assign("kaohe1",$kaohe1);
        $this->assign("k1id",$k1id);

        $where="y.type=2 and y.jine<>0 and y.cainian=$cid ";
        if($k1id){
            $where.="and y.yijikaoheqi=$k1id "; 
        }
        $ys=db("yusuan y")->where($where)->sum("y.jine");
        $pf=db("yusuan y")->join("yusuan_detail yd","yd.yid=y.id")->where($where."and yd.type=2")->sum("yd.jine");
        $sq=db("yusuan y")->join("yusuan_detail yd","yd.yid=y.id")->where($where."and yd.type=1")->sum("yd.jine");
        if($ys){
            $apply["ys"]=round($ys,2);
        }else{
            $apply["ys"]=0;
        }
        if($pf){
            $apply["pf"]=round($pf,2);
        }else{
            $apply["pf"]=0;
        }
        $apply["sy"]=$apply["ys"]-$apply["pf"];
        if($sq){
            $apply["sq"]=round($sq,2);
        }else{
            $apply["sq"]=0;
        }
        if($apply["pf"]>=$apply["ys"]){
            $apply["deng"]=3;//红灯
        }
        else if($apply["pf"]+$apply["sq"]<$apply["ys"]){
            $apply["deng"]=1;//绿灯
        }else{
            $apply["deng"]=2;//黄灯
        }
        $this->assign("apply",$apply);
        return $this->fetch();
    }
    public function yssydc($cid,$k1id){
        $where="y.type=2 and y.jine<>0 and y.cainian=$cid ";
        if($k1id){
            $where.="and y.yijikaoheqi=$k1id "; 
        }
        $ys=db("yusuan y")->where($where)->sum("y.jine");
        $pf=db("yusuan y")->join("yusuan_detail yd","yd.yid=y.id")->where($where."and yd.type=2")->sum("yd.jine");
        $sq=db("yusuan y")->join("yusuan_detail yd","yd.yid=y.id")->where($where."and yd.type=1")->sum("yd.jine");
        if($ys){
            $apply["ys"]=round($ys,2);
        }else{
            $apply["ys"]=0;
        }
        if($pf){
            $apply["pf"]=round($pf,2);
        }else{
            $apply["pf"]=0;
        }
        $apply["sy"]=$apply["ys"]-$apply["pf"];
        if($sq){
            $apply["sq"]=round($sq,2);
        }else{
            $apply["sq"]=0;
        }
        if($apply["pf"]>=$apply["ys"]){
            $apply["deng"]=3;//红灯
        }
        else if($apply["pf"]+$apply["sq"]<$apply["ys"]){
            $apply["deng"]=1;//绿灯
        }else{
            $apply["deng"]=2;//黄灯
        }
        //导出处理
        $eid=Session::get("employee_id");
        $users=Db::table("employee")->where(["id"=>$eid])->find();
        $title="AP费用预算使用报表";
        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
         $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
             $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);
       
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);

       

        
          $objPHPExcel->getActiveSheet()->mergeCells('A1:E1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "$title");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:E2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
     
      


         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"预算金额");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"已批复金额");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"未使用金额");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"申请中金额");
        
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A4',"1",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B4',$apply["ys"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C4',$apply["pf"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D4',$apply["sy"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E4',$apply["sq"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A5',"总计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B5',$apply["ys"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C5',$apply["pf"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D5',$apply["sy"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E5',$apply["sq"],PHPExcel_Cell_DataType::TYPE_STRING);
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:E5')->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
             //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
               $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        //var_dump($objWriter);exit;
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
}